/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facade;

import Beans.InventoryStoreBean;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import newkfp.AbstractFacade;
import newkfp.Bill;
import newkfp.BillItem;
import newkfp.Item;

/**
 *
 * @author A7med Kotb
 */
@Stateless
public class InventoryStoreFacade extends AbstractFacade<InventoryStoreBean> {

    @PersistenceContext(unitName = "KFPNEWPU")
    private EntityManager em;

    protected EntityManager getEntityManager() {
        return em;
    }

    public InventoryStoreFacade() {
        super(InventoryStoreBean.class);
    }

    public List<String> getItemTypes() {
        Query query = em.createNamedQuery("Item.findItemType");
        return query.getResultList();
    }

    public List<Item> getAllItems() {
        Query query = em.createNamedQuery("Item.findAll");
        return query.getResultList();
    }

    public List<Bill> getExporterBillNT(int i, Date dateNow, Date toDate) {
        Query query = getEntityManager().createNativeQuery("SELECT * FROM bill b  WHERE b.customerID = ? And date(b.createDate) between ? And ? ", Bill.class);
        query.setParameter(1, i);
        query.setParameter(2, toDate);
        query.setParameter(3, dateNow);
        List<Bill> list = query.getResultList();
        return list;
    }

    public BillItem getItemAmount(int itemID, int billID) {
        try {
            Query query = getEntityManager().createNativeQuery("SELECT * FROM bill_item  WHERE itemID = ? And billID = ? ", BillItem.class);
            query.setParameter(1, itemID);
            query.setParameter(2, billID);
            List<BillItem> billItems = query.getResultList();
            BillItem billItem1 = new BillItem();
            double quantity = 0;
            double amount = 0;
            double price = 0;
            for (Iterator<BillItem> it = billItems.iterator(); it.hasNext();) {
                BillItem billItem = it.next();
                amount += billItem.getItemAmount();
                quantity += billItem.getPackageCount();
                price += billItem.getItemPrice();
            }
            billItem1.setItemAmount(quantity);
            billItem1.setPackageCount(amount);
            billItem1.setItemPrice(price);
            return billItem1;
        } catch (Exception e) {
            return null;
        }
    }

    public List<Bill> getCustomerBillNT(int i, Date nowDate, Date toDate) {
        Query query = getEntityManager().createNativeQuery("SELECT * FROM bill  WHERE exporterID = ? And date(createDate) between ? And ? ", Bill.class);
        query.setParameter(1, i);
        query.setParameter(2, toDate);
        query.setParameter(3, nowDate);
        List<Bill> list = query.getResultList();
        return list;
    }

    public Double getItemAvg(int itemID, int i, Date fromDate, Date toDate) {
        Query query = getEntityManager().createNativeQuery("SELECT avg(bi.itemPrice) FROM bill_item bi,bill b  WHERE b.idBill=bi.billID and bi.itemID=? and b.exporterID = ? And date(b.createDate) between ? And ? ");
        query.setParameter(1, itemID);
        query.setParameter(2, i);
        query.setParameter(3, fromDate);
        query.setParameter(4, toDate);
        if (query.getResultList().get(0) == null) {
            return 0.0;
        } else {
            return (Double) query.getResultList().get(0);
        }
    }
}